# -*- coding: utf-8 -*-
__author__ = 'sunnychou'
__date__ = '2019/9/18 14:29'
import os
import shutil
import arrow
from common.utils import create_uuid
from common.mssql_helper import g_msql_inst
from collections import defaultdict
from common.excel_helper import XLSXBooker, ExcelReader
from common.logger_helper import g_wlogger
from k3_data_report.config import k3_base_table_fields

def get_wj(sql):
    res_list1 = g_msql_inst.fetch_all(sql)
    dic = defaultdict(dict)
    for (wjj, wjmc, sjsj, sftq) in res_list1:
        dic[wjmc] = [wjj, wjmc, sjsj, sftq]
    return dic


def insert_k3_dp_tb(excel_file, biaoqian, db_insert_sql, ineert_time):
    file = ExcelReader(excel_file, biaoqian)
    list = file.get_excel_data_list()
    for i in list:
        try:
            id = create_uuid()
            i.append(id)
            i.append(ineert_time)
            inseet_val = tuple(i)
            excuete_sql = db_insert_sql % inseet_val
            g_msql_inst.db_excuete(excuete_sql)
        except Exception as  e:
            g_wlogger.werror(f"insert_k3_dp_tb has error:{e} with excel_file:{excel_file}, insert_time:{ineert_time}")

def update_k3_dp_tb(wjmc, insert_time):
    try:
        excuete_sql = "update k3wjsm set sftq='Y' WHERE  wjmc='%s'  and sjsj='%s'" % (wjmc, insert_time)
        g_msql_inst.db_excuete(excuete_sql)
    except Exception as e:
        g_wlogger.werror(f"update_k3_dp_tb has error:{e} with wjmc:{wjmc}, insert_time:{insert_time}")

def insert_scan_wj(db_insert_sql, value_list):
    id = create_uuid()
    value_list.append(id)
    value_tuple = tuple(value_list)
    excuete_sql = db_insert_sql % value_tuple
    print(excuete_sql)
    g_msql_inst.db_excuete(excuete_sql)

def charge_excel_pretype(excel_file):
    '''
    获取excel文件的类别
    :param excel_file:
    :return:
    '''
    try:
        efiles = excel_file.split("-")
        file_type = efiles[0]
        year = efiles[1]
        month = efiles[2].split(".")[0]
        file_date = "%s-%s" % (year, month)
        return (file_type, file_date)
    except Exception as e:
        g_wlogger.werror(f"charge_excel_pretype has error :{e} , may be file:{excel_file} format is error.")
        return None


db_sql= k3_base_table_fields["saomia_insert"]
check_sql=k3_base_table_fields['check_sql']["checkwj_sql"]

def  check_excel_file_valid(excel_file):
    '''
    检测excel文件命名是否合法
    :return:
    '''
    try:
        split_finfos = excel_file.split("-")
        if len(split_finfos) < 3:
            g_wlogger.werror(f"sorry, the file:{excel_file} format is error.")
            return False
        return True
    except Exception as e:
        g_wlogger.werror(f"sorry, the file:{excel_file} check_excel_file_valid is invalid with:{e}")
        return False


class  Scanfiles(object):
  @staticmethod
  def file_name(file_dir):
    wj=get_wj(check_sql)
    insert_time1 = arrow.now().shift(months=-1).format("YYYY-MM")
    for root, dirs, files in os.walk(file_dir):
      dqwjj=root.split("\\")[-1]
      # print(root)
      # print(dqwjj) #当前目录路径
      # print(dirs) #当前路径下所有子目录
      # print( files) #当前路径下所有非目录子文件
      for i in files:
        if i in wj.keys():
          addr = file_dir +  "\\" + wj[i][0] + "\\" + wj[i][1]
          g_wlogger.winfo(f"sorry, the file:{addr} already existed in db")
        else:
          lis=[]
          if not check_excel_file_valid(i):
              continue
          if len(i.split("-"))==3:
            a=i.split("-")[2]
            insert_time=i.split("-")[1]+"-"+a.split(".")[0]
            lis.extend( [dqwjj, i, insert_time,'N'])
            insert_scan_wj(db_sql,lis)
          else:
            lis.extend([dqwjj,i,insert_time1,'N'])
            insert_scan_wj(db_sql,lis)

#lis=Scanfiles.file_name(file_add)

